package query.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import query.bean.Question;
import query.bean.Questionnaire;
import query.db.QuestionnaireRepository;

/**
 * 问卷数据库操作接口实现类
 * 
 * @author lzy
 * @version 1.0.4
 *
 */
@Repository
public class JdbcQuestionnaireRepository implements QuestionnaireRepository {

	private JdbcTemplate jdbc;
	private final static String INSERT_QUESTION = "INSERT INTO Question (questionnaireId,description,type,orderNumber,choiceA,choiceB,choiceC,choiceD)VALUES(?,?,?,?,?,?,?,?)";
	private final static String DELETE_QUESTION_BY_QUESTIONNAIRE_ID = "DELETE FROM Question WHERE questionnaireId = ? ";
	private final static String SEARCH_QUESTION_BY_ORDER = "SELECT* FROM Question WHERE questionnaireId = ? ORDER BY orderNumber";
	private final static String SELECT_ID = "SELECT id FROM QuestionnaireId ORDER BY id LIMIT 0,1";
	private final static String DELETE_ID = "DELETE FROM QuestionnaireId WHERE id = ? ";
	private final static String INSERT_ID = "INSERT INTO QuestionnaireId (id)VALUES(?)";
	private final static String INSERT_QUESTIONNAIRE = "INSERT INTO Questionnaire (id,title,account,submitTime,close)VALUES(?,?,?,?,?)";
	private final static String SEARCHE_QUESTIONNAIRE_BY_INDEX = "SELECT* FROM Questionnaire ORDER BY id DESC LIMIT ";
	private final static String SEARCH_QUESTIONNAIRE_BY_ACCOUNT = "SELECT* FROM Questionnaire WHERE account = ? ORDER BY id DESC LIMIT ";
	private final static String DELETE_QUESTIONNAIRE_BY_ID = "DELETE FROM Questionnaire WHERE id = ?";
	private final static String SEARCH_QUESTIONNAIRE_BY_ID = "SELECT* FROM Questionnaire WHERE id = ?";
	private final static String SEARCH_QUESTIONNAIRES = "SELECT* FROM Questionnaire WHERE account = ?";
	private final static String UPDATE_CLOSE = "UPDATE Questionnaire SET close = ? WHERE id = ?";
	private final static String SEARCH_QUESTIONNAIRE_BY_TITLE = "SELECT* FROM Questionnaire WHERE title LIKE ? LIMIT ";

	/**
	 * 构造方法注入
	 * 
	 * @param jdbc
	 */
	@Autowired
	public JdbcQuestionnaireRepository(JdbcTemplate jdbc) {
		this.jdbc = jdbc;
	}

	/**
	 * 插入一道问题
	 * 
	 * @param question 要插入的问题
	 * @return 操作结果
	 */
	@Override
	public boolean insertQuestion(Question question) {
		try {
			jdbc.update(INSERT_QUESTION, question.getQuestionnaireId(), question.getDescription(), question.getType(),
					question.getOrderNumber(), question.getChoiceA(), question.getChoiceB(), question.getChoiceC(),
					question.getChoiceD());
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	/**
	 * 删除一个问卷的所有问题
	 * 
	 * @param questionnaireId 问卷id
	 * @return 操作结果
	 */
	@Override
	public boolean deleteQuestion(long questionnaireId) {
		try {
			jdbc.update(DELETE_QUESTION_BY_QUESTIONNAIRE_ID, questionnaireId);
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	/**
	 * 删除一个问卷
	 * 
	 * @param questionnaireId 问卷id
	 * @return 操作结果
	 */
	@Override
	public boolean deleteQuestionnaire(long questionnaireId) {
		this.deleteQuestion(questionnaireId);
		jdbc.update(DELETE_QUESTIONNAIRE_BY_ID, questionnaireId);
		jdbc.update(INSERT_ID, questionnaireId);
		return true;
	}

	/**
	 * 新建一个问卷
	 * 
	 * @param account 问卷创建用户用户名
	 * @param title   问卷标题
	 * @return 操作结果
	 */
	@Override
	public Questionnaire insertQuestionnaire(String account, String title) {
		long id = this.createId();
		if (id == -1) {
			return null;
		}
		Questionnaire questionnaire = new Questionnaire(id, account, title, new Date(), false);
		jdbc.update(INSERT_QUESTIONNAIRE, questionnaire.getId(), questionnaire.getTitle(), questionnaire.getAccount(),
				questionnaire.getSubmitTime(), questionnaire.isClose());
		return questionnaire;
	}

	/**
	 * 查找指定范围的问卷
	 * 
	 * @param account 问卷所属用户
	 * @param start   起始下标()不包括在内
	 * @param num     要查询的问卷数目
	 * @return 问卷列表
	 */
	@Override
	public List<Questionnaire> searchQuestionnaires(String account, int start, int num) {
		List<Questionnaire> list;
		try {
			list = jdbc.query(SEARCH_QUESTIONNAIRE_BY_ACCOUNT + start + "," + num, new QuestionnaireRowMapper(), account);
		} catch (Exception e) {
			return null;
		}
		return list;
	}

	/**
	 * 根据id查找问卷
	 * 
	 * @param questionnairesId 问卷id
	 * @return 查询结果
	 */
	@Override
	public Questionnaire searchQuestionnaires(long questionnairesId) {
		Questionnaire questionnaire;
		try {
			questionnaire = jdbc.queryForObject(SEARCH_QUESTIONNAIRE_BY_ID, new QuestionnaireRowMapper(), questionnairesId);
		} catch (Exception e) {
			return null;
		}
		return questionnaire;
	}

	/**
	 * 更新问卷状态
	 * 
	 * @param questionnaireId 问卷id
	 * @param close           要更新的状态值
	 * @return 操作结果
	 */
	@Override
	public boolean updateClose(long questionnaireId, boolean close) {
		jdbc.update(UPDATE_CLOSE, close, questionnaireId);
		return true;
	}

	/**
	 * 获取指定问卷的所有问题
	 * 
	 * @param questionnaireId 问卷id
	 * @return 问题列表
	 */
	@Override
	public List<Question> searchQuestions(long questionnaireId) {
		List<Question> list;
		try {
			list = jdbc.query(SEARCH_QUESTION_BY_ORDER, new QuestionRowMapper(), questionnaireId);
		} catch (Exception e) {
			return null;
		}
		return list;
	}

	/**
	 * 查找一个用户的所有问卷
	 * 
	 * @param account 用户名
	 * @return 问卷列表
	 */
	@Override
	public List<Questionnaire> searchQuestionnaires(String account) {
		List<Questionnaire> list;
		try {
			list = jdbc.query(SEARCH_QUESTIONNAIRES, new QuestionnaireRowMapper(), account);
		} catch (Exception e) {
			return null;
		}
		return list;
	}

	/**
	 * 根据问卷标题模糊查询问卷
	 * 
	 * @param title 问卷标题
	 * @return 查询结果
	 */
	@Override
	public List<Questionnaire> searchQuestionnaire(String title, int start, int end) {
		List<Questionnaire> list;
		try {
			list = jdbc.query(SEARCH_QUESTIONNAIRE_BY_TITLE + start + "," + end, new QuestionnaireRowMapper(),
					"%" + title + "%");
		} catch (Exception e) {
			return null;
		}
		return list;
	}

	/**
	 * 生成问卷id
	 * 
	 * @return 问卷id
	 */
	@SuppressWarnings("deprecation")
	private long createId() {
		long id;
		try {
			id = jdbc.queryForLong(SELECT_ID);
			jdbc.update(DELETE_ID, id);
		} catch (Exception e) {
			/* id值用完 */
			return -1;
		}
		return id;
	}

	/**
	 * 查找一定范围内的全部问卷
	 * 
	 * @param start 起始下标(不包括在内)
	 * @param num   要查询的问卷数目
	 * @return 问卷列表
	 */
	public List<Questionnaire> searchQuestionnaires(int start, int end) {
		List<Questionnaire> list;
		try {
			list = jdbc.query(SEARCHE_QUESTIONNAIRE_BY_INDEX + start + "," + end, new QuestionnaireRowMapper());
		} catch (Exception e) {
			return null;
		}
		return list;
	}

	/**
	 * 用于查询问卷
	 * 
	 * @author lzy
	 * @version 1.0.4
	 *
	 */
	private static final class QuestionnaireRowMapper implements RowMapper<Questionnaire> {
		@Override
		public Questionnaire mapRow(ResultSet rs, int rowNum) throws SQLException {
			return new Questionnaire(rs.getLong("id"), rs.getString("account"), rs.getString("title"),
					rs.getDate("submitTime"), rs.getBoolean("close"));
		}

	}

	/**
	 * 用于查询问题
	 * 
	 * @author lzy
	 * @version 1.0.4
	 *
	 */
	private static final class QuestionRowMapper implements RowMapper<Question> {

		@Override
		public Question mapRow(ResultSet rs, int rowNum) throws SQLException {
			return new Question(rs.getLong("questionnaireId"), rs.getString("description"), rs.getInt("type"),
					rs.getInt("orderNumber"), rs.getString("choiceA"), rs.getString("choiceB"), rs.getString("choiceC"),
					rs.getString("choiceD"));
		}

	}
}
